import com.krm.ExcelBean;
import com.krm.TableBean;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ：lgw
 * @date ：Created in 2019/9/6 15:46
 * @description ：   描述
 * @modified By ：
 * @version: 1
 */
public class PinYI {

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";


    public static void main(String[] args) throws  Exception{
        myRun("/test/test.xlsx","@krmsoft.com");
    }

    public  static void myRun(String filePath,String emailNet) throws Exception {
        List<String> list = readExcel(filePath);
//        Map<String,Object> map = new HashMap<String,Object>();
        List<Map<String,String>> listMap = new ArrayList<Map<String,String>>();
        for(String str : list ){

            Map<String,String> map = new HashMap<String,String>();
            String pinyi= getPinYin(str);
//            String email = pinyi+"@krmsoft.com";
            if(emailNet!=""&&emailNet!=null){
                String email = pinyi+emailNet;
                map.put("email",email);
            }
            map.put("name",str);
            map.put("pinyi",pinyi);

            //map.put(str,map2);
            listMap.add(map);
        }
        if(filePath==null||filePath==""){
            throw new Exception("未得到文件名及路径");
        }
        /*String filePathResult = "";
        if(filePath.contains(EXCEL_XLSX)){
            filePathResult = filePath.replace("."+EXCEL_XLSX,"");
            filePathResult = filePathResult + "_结果"+"."+EXCEL_XLSX;
        }else{
            filePathResult = filePath.replace("."+EXCEL_XLS,"");
            filePathResult = filePathResult + "_结果"+"."+EXCEL_XLS;
        }*/

        writeExcel(listMap, list.size(),filePath);
        System.out.println("----------生成完成："+filePath);
    }

    public static String getPinYin(String src) {
        char[] hz = null;
        hz = src.toCharArray();//该方法的作用是返回一个字符数组，该字符数组中存放了当前字符串中的所有字符
        String[] py = new String[hz.length];//该数组用来存储
        //设置汉子拼音输出的格式
        HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
        format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        format.setVCharType(HanyuPinyinVCharType.WITH_V);

        String pys = ""; //存放拼音字符串
        int len = hz.length;

        try {
            for (int i = 0; i < len; i++) {
                //先判断是否为汉字字符
                if (Character.toString(hz[i]).matches("[\\u4E00-\\u9FA5]+")) {
                    //将汉字的几种全拼都存到py数组中
                    py = PinyinHelper.toHanyuPinyinStringArray(hz[i], format);
                    //取出改汉字全拼的第一种读音，并存放到字符串pys后
                    pys += py[0];
                } else {
                    //如果不是汉字字符，间接取出字符并连接到 pys 后
                    pys += Character.toString(hz[i]);
                }
            }
        } catch (BadHanyuPinyinOutputFormatCombination e) {
            e.printStackTrace();
        }
        return pys;
    }



    public static  List<String> readExcel(String path){

        List<String> list = new ArrayList<String>();

        try {
            InputStream is =  new FileInputStream(path);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

            //读sheet
            for (int numSheet = 0; numSheet <xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if(xssfSheet==null){
                    continue;
                }

                for(int rowNum=1;rowNum<=xssfSheet.getLastRowNum();rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow!=null){
//                        System.out.println("----"+xssfRow.getCell(0).getStringCellValue());
                        String str = xssfRow.getCell(0).getStringCellValue();
                        list.add(str);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }




    public static void writeExcel(List<Map<String,String>> dataList, int cloumnCount,String finalXlsxPath){
        OutputStream out = null;
        try {
            // 获取总列数
            int columnNumCount = cloumnCount;
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);
            /**
             * 删除原有数据，除了属性列
             */
            int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
            System.out.println("原始数据总行数，除属性列：" + rowNumber);
            for (int i = 1; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);
                sheet.removeRow(row);
            }
            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out =  new FileOutputStream(finalXlsxPath);
            workBook.write(out);
            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < dataList.size(); j++) {
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 1);
                // 得到要插入的每一条记录
                Map dataMap = dataList.get(j);

//                map.put("name",str);
//                map.put("pinyi",pinyi);
//                map.put("email",email);

                String name = dataMap.get("name").toString();
                String pinyi = dataMap.get("pinyi").toString();
                String email = "";
                if(dataMap.get("email")!=null){
                     email = dataMap.get("email").toString();
                }

                for (int k = 0; k <= columnNumCount; k++) {
                    // 在一行内循环
                    Cell first = row.createCell(0);
                    first.setCellValue(name);

                    Cell second = row.createCell(1);
                    second.setCellValue(pinyi);

                    if(email != ""){
                        Cell third = row.createCell(2);
                        third.setCellValue(email);
                    }

                }
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out =  new FileOutputStream(finalXlsxPath);
            workBook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(out != null){
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("数据导出成功");
    }

    /**
     * 判断Excel的版本,获取Workbook
     * @param
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if(file.getName().endsWith(EXCEL_XLS)){     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        }else if(file.getName().endsWith(EXCEL_XLSX)){    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }


}
